package com.techzhi.address_web;

import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.ObjectMapper;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.junit.jupiter.api.Test;

import java.io.*;
import java.net.HttpURLConnection;
import java.net.URL;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;

/**
 * 可靠的数据生成工具
 * 不使用缓存，直接调用API获取完整数据
 */
public class GenerateReliableData {

    // 高德地图API配置
    private static final String AMAP_KEY = "07f189ecd1b56b096ac51860eecc5a06";
    private static final String AMAP_BASE_URL = "https://restapi.amap.com/v3/config/district";
    
    @Data
    @NoArgsConstructor
    @AllArgsConstructor
    static class AreaData {
        private String code;
        private String name;
        private String parentCode;
        private int level; // 1=省, 2=市, 3=区县
    }
    
    @Test
    public void generateReliableData() {
        try {
            System.out.println("开始生成可靠的省市区数据（无缓存模式）...");
            
            // 获取所有数据
            List<AreaData> allAreas = fetchAllDistrictDataReliable();
            System.out.println("获取数据完成，共 " + allAreas.size() + " 条记录");
            
            // 分类数据
            List<AreaData> provinces = new ArrayList<>();
            List<AreaData> cities = new ArrayList<>(); 
            List<AreaData> districts = new ArrayList<>();
            
            for (AreaData area : allAreas) {
                switch (area.getLevel()) {
                    case 1: provinces.add(area); break;
                    case 2: cities.add(area); break;
                    case 3: districts.add(area); break;
                }
            }
            
            System.out.println("数据分类完成 - 省:" + provinces.size() + ", 市:" + cities.size() + ", 区县:" + districts.size());
            
            // 验证数据完整性
            validateDataIntegrity(provinces, cities, districts);
            
            // 生成SQL文件
            generateMySQLFile(provinces, cities, districts);
            generatePostgreSQLFile(provinces, cities, districts);
            
            System.out.println("✅ 可靠数据生成完成!");
            
        } catch (Exception e) {
            System.err.println("❌ 生成数据时发生错误: " + e.getMessage());
            e.printStackTrace();
        }
    }
    
    /**
     * 可靠地获取所有行政区划数据
     */
    private List<AreaData> fetchAllDistrictDataReliable() throws Exception {
        List<AreaData> allAreas = new ArrayList<>();
        ObjectMapper mapper = new ObjectMapper();
        
        // 1. 获取所有省份
        System.out.println("正在获取省份数据...");
        String provinceResponse = callAmapApiReliable("", 1);
        JsonNode provinceJson = mapper.readTree(provinceResponse);
        
        if (!provinceJson.get("status").asText().equals("1")) {
            throw new Exception("获取省份数据失败: " + provinceJson.get("info").asText());
        }
        
        JsonNode provinces = provinceJson.get("districts").get(0).get("districts");
        System.out.println("获取到 " + provinces.size() + " 个省份");
        
        for (JsonNode province : provinces) {
            String provinceCode = province.get("adcode").asText();
            String provinceName = province.get("name").asText();
            
            // 转换为6位编码
            String standardProvinceCode = convertTo6DigitCode(provinceCode, 1);
            allAreas.add(new AreaData(standardProvinceCode, provinceName, "", 1));
            
            System.out.println("处理省份: " + provinceName + " (" + standardProvinceCode + ")");
            
            // 2. 获取该省份下的所有城市
            try {
                String cityResponse = callAmapApiReliable(provinceCode, 2);
                JsonNode cityJson = mapper.readTree(cityResponse);
                
                if (cityJson.get("status").asText().equals("1")) {
                    JsonNode cities = cityJson.get("districts").get(0).get("districts");
                    System.out.println("  获取到 " + cities.size() + " 个城市");
                    
                    for (JsonNode city : cities) {
                        String cityCode = city.get("adcode").asText();
                        String cityName = city.get("name").asText();
                        
                        String standardCityCode = convertTo6DigitCode(cityCode, 2);
                        allAreas.add(new AreaData(standardCityCode, cityName, standardProvinceCode, 2));
                        
                        System.out.println("  处理城市: " + cityName + " (" + standardCityCode + ")");
                        
                        // 3. 获取该城市下的所有区县
                        try {
                            String districtResponse = callAmapApiReliable(cityCode, 3);
                            JsonNode districtJson = mapper.readTree(districtResponse);
                            
                            if (districtJson.get("status").asText().equals("1")) {
                                JsonNode districts = districtJson.get("districts").get(0).get("districts");
                                System.out.println("    获取到 " + districts.size() + " 个区县");
                                
                                int districtIndex = 1;
                                for (JsonNode district : districts) {
                                    String districtCode = district.get("adcode").asText();
                                    String districtName = district.get("name").asText();
                                    
                                    String standardDistrictCode = convertTo6DigitCode(districtCode, 3);
                                    
                                    // 检查区县编码是否与城市编码重复
                                    if (standardDistrictCode.equals(standardCityCode)) {
                                        standardDistrictCode = standardCityCode + String.format("%03d", districtIndex);
                                        System.out.println("    检测到重复编码，为 " + cityName + " 的 " + districtName + " 生成新编码: " + standardDistrictCode);
                                    }
                                    
                                    allAreas.add(new AreaData(standardDistrictCode, districtName, standardCityCode, 3));
                                    districtIndex++;
                                }
                            } else {
                                System.err.println("    获取区县数据失败: " + districtJson.get("info").asText());
                            }
                        } catch (Exception e) {
                            System.err.println("    获取区县数据异常: " + e.getMessage());
                        }
                        
                        // 避免请求过于频繁
                        Thread.sleep(200);
                    }
                } else {
                    System.err.println("  获取城市数据失败: " + cityJson.get("info").asText());
                }
            } catch (Exception e) {
                System.err.println("  获取城市数据异常: " + e.getMessage());
            }
            
            // 避免请求过于频繁
            Thread.sleep(200);
        }
        
        return allAreas;
    }
    
    /**
     * 可靠地调用高德地图API
     */
    private String callAmapApiReliable(String keywords, int subdistrict) throws Exception {
        StringBuilder urlBuilder = new StringBuilder(AMAP_BASE_URL);
        urlBuilder.append("?key=").append(AMAP_KEY);
        urlBuilder.append("&subdistrict=").append(subdistrict);
        urlBuilder.append("&extensions=base");
        
        if (!keywords.isEmpty()) {
            urlBuilder.append("&keywords=").append(URLEncoder.encode(keywords, StandardCharsets.UTF_8));
        }
        
        URL url = new URL(urlBuilder.toString());
        HttpURLConnection connection = (HttpURLConnection) url.openConnection();
        connection.setRequestMethod("GET");
        connection.setConnectTimeout(15000);
        connection.setReadTimeout(60000);
        connection.setRequestProperty("User-Agent", "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36");
        
        // 重试机制
        int maxRetries = 3;
        int retryCount = 0;
        
        while (retryCount < maxRetries) {
            try {
                StringBuilder response = new StringBuilder();
                try (BufferedReader reader = new BufferedReader(new InputStreamReader(connection.getInputStream(), StandardCharsets.UTF_8))) {
                    String line;
                    while ((line = reader.readLine()) != null) {
                        response.append(line);
                    }
                }
                
                String responseStr = response.toString();
                
                // 验证响应是否有效
                ObjectMapper mapper = new ObjectMapper();
                JsonNode json = mapper.readTree(responseStr);
                if (json.has("status") && json.get("status").asText().equals("1")) {
                    return responseStr;
                } else {
                    throw new Exception("API返回错误: " + json.get("info").asText());
                }
                
            } catch (Exception e) {
                retryCount++;
                if (retryCount >= maxRetries) {
                    throw e;
                }
                System.err.println("API调用失败，第" + retryCount + "次重试: " + e.getMessage());
                Thread.sleep(1000 * retryCount); // 递增延迟
            }
        }
        
        throw new Exception("API调用失败，已达到最大重试次数");
    }
    
    /**
     * 转换为6位编码
     */
    private String convertTo6DigitCode(String originalCode, int level) {
        if (originalCode.length() >= 6) {
            return originalCode.substring(0, 6);
        }
        return originalCode;
    }
    
    /**
     * 验证数据完整性
     */
    private void validateDataIntegrity(List<AreaData> provinces, List<AreaData> cities, List<AreaData> districts) {
        System.out.println("=== 数据完整性验证 ===");
        
        // 检查编码唯一性
        Set<String> allCodes = new HashSet<>();
        boolean hasDuplicate = false;
        
        for (AreaData province : provinces) {
            if (!allCodes.add(province.getCode())) {
                System.err.println("❌ 省份编码重复: " + province.getCode() + " (" + province.getName() + ")");
                hasDuplicate = true;
            }
        }
        
        for (AreaData city : cities) {
            if (!allCodes.add(city.getCode())) {
                System.err.println("❌ 城市编码重复: " + city.getCode() + " (" + city.getName() + ")");
                hasDuplicate = true;
            }
        }
        
        for (AreaData district : districts) {
            if (!allCodes.add(district.getCode())) {
                System.err.println("❌ 区县编码重复: " + district.getCode() + " (" + district.getName() + ")");
                hasDuplicate = true;
            }
        }
        
        if (!hasDuplicate) {
            System.out.println("✅ 编码唯一性检查通过");
        }
        
        // 检查关联关系
        Map<String, AreaData> provinceMap = new HashMap<>();
        for (AreaData province : provinces) {
            provinceMap.put(province.getCode(), province);
        }
        
        Map<String, AreaData> cityMap = new HashMap<>();
        for (AreaData city : cities) {
            cityMap.put(city.getCode(), city);
        }
        
        int orphanCities = 0;
        for (AreaData city : cities) {
            if (!provinceMap.containsKey(city.getParentCode())) {
                System.err.println("❌ 城市 " + city.getName() + " 的父级省份不存在: " + city.getParentCode());
                orphanCities++;
            }
        }
        
        int orphanDistricts = 0;
        for (AreaData district : districts) {
            if (!cityMap.containsKey(district.getParentCode())) {
                System.err.println("❌ 区县 " + district.getName() + " 的父级城市不存在: " + district.getParentCode());
                orphanDistricts++;
            }
        }
        
        // 统计每个省份的城市数量
        Map<String, Integer> provinceCityCount = new HashMap<>();
        for (AreaData city : cities) {
            provinceCityCount.put(city.getParentCode(), provinceCityCount.getOrDefault(city.getParentCode(), 0) + 1);
        }
        
        System.out.println("\n=== 省份城市统计 ===");
        for (AreaData province : provinces) {
            int cityCount = provinceCityCount.getOrDefault(province.getCode(), 0);
            System.out.println(province.getName() + " (" + province.getCode() + "): " + cityCount + " 个城市");
            if (cityCount == 0) {
                System.err.println("❌ 省份 " + province.getName() + " 下没有城市数据！");
            }
        }
        
        System.out.println("\n=== 数据完整性总结 ===");
        System.out.println("总省份数: " + provinces.size());
        System.out.println("总城市数: " + cities.size());
        System.out.println("总区县数: " + districts.size());
        System.out.println("孤立城市数: " + orphanCities);
        System.out.println("孤立区县数: " + orphanDistricts);
        System.out.println("编码重复: " + (hasDuplicate ? "是" : "否"));
        
        if (orphanCities == 0 && orphanDistricts == 0 && !hasDuplicate) {
            System.out.println("✅ 数据完整性验证通过");
        } else {
            System.err.println("❌ 数据完整性验证失败");
        }
    }
    
    /**
     * 生成MySQL SQL文件
     */
    private void generateMySQLFile(List<AreaData> provinces, List<AreaData> cities, List<AreaData> districts) throws IOException {
        String timestamp = LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
        String filePath = "/Users/shouzhi/techzhi/project/gitee/techzhi-common/address-system/scripts/back/mysql_data_reliable.sql";
        
        try (PrintWriter writer = new PrintWriter(new FileWriter(filePath, StandardCharsets.UTF_8))) {
            writer.println("-- 可靠的省市区数据");
            writer.println("-- 生成时间: " + timestamp);
            writer.println("-- 数据统计: 省份" + provinces.size() + "个, 城市" + cities.size() + "个, 区县" + districts.size() + "个");
            writer.println();
            
            // 清空现有数据
            writer.println("-- 清空现有数据");
            writer.println("DELETE FROM district;");
            writer.println("DELETE FROM city;");
            writer.println("DELETE FROM province;");
            writer.println();
            
            // 插入省份数据
            writer.println("-- 插入省份数据");
            for (AreaData province : provinces) {
                writer.printf("INSERT INTO province (code, name, create_time) VALUES ('%s', '%s', NOW());%n", 
                    province.getCode(), province.getName());
            }
            writer.println();
            
            // 插入城市数据
            writer.println("-- 插入城市数据");
            for (AreaData city : cities) {
                writer.printf("INSERT INTO city (code, name, province_code, create_time) VALUES ('%s', '%s', '%s', NOW());%n", 
                    city.getCode(), city.getName(), city.getParentCode());
            }
            writer.println();
            
            // 插入区县数据
            writer.println("-- 插入区县数据");
            for (AreaData district : districts) {
                writer.printf("INSERT INTO district (code, name, city_code, create_time) VALUES ('%s', '%s', '%s', NOW());%n", 
                    district.getCode(), district.getName(), district.getParentCode());
            }
            writer.println();
            
            writer.println("-- 数据插入完成");
            writer.printf("-- 统计: 省份 %d 个, 城市 %d 个, 区县 %d 个%n", provinces.size(), cities.size(), districts.size());
        }
        
        System.out.println("MySQL SQL文件已生成: " + filePath);
    }
    
    /**
     * 生成PostgreSQL SQL文件
     */
    private void generatePostgreSQLFile(List<AreaData> provinces, List<AreaData> cities, List<AreaData> districts) throws IOException {
        String timestamp = LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
        String filePath = "/Users/shouzhi/techzhi/project/gitee/techzhi-common/address-system/scripts/back/postgresql_data_reliable.sql";
        
        try (PrintWriter writer = new PrintWriter(new FileWriter(filePath, StandardCharsets.UTF_8))) {
            writer.println("-- 可靠的省市区数据");
            writer.println("-- 生成时间: " + timestamp);
            writer.println("-- 数据统计: 省份" + provinces.size() + "个, 城市" + cities.size() + "个, 区县" + districts.size() + "个");
            writer.println();
            
            // 清空现有数据
            writer.println("-- 清空现有数据");
            writer.println("DELETE FROM district;");
            writer.println("DELETE FROM city;");
            writer.println("DELETE FROM province;");
            writer.println();
            
            // 插入省份数据
            writer.println("-- 插入省份数据");
            for (AreaData province : provinces) {
                writer.printf("INSERT INTO province (code, name, create_time) VALUES ('%s', '%s', NOW());%n", 
                    province.getCode(), province.getName());
            }
            writer.println();
            
            // 插入城市数据
            writer.println("-- 插入城市数据");
            for (AreaData city : cities) {
                writer.printf("INSERT INTO city (code, name, province_code, create_time) VALUES ('%s', '%s', '%s', NOW());%n", 
                    city.getCode(), city.getName(), city.getParentCode());
            }
            writer.println();
            
            // 插入区县数据
            writer.println("-- 插入区县数据");
            for (AreaData district : districts) {
                writer.printf("INSERT INTO district (code, name, city_code, create_time) VALUES ('%s', '%s', '%s', NOW());%n", 
                    district.getCode(), district.getName(), district.getParentCode());
            }
            writer.println();
            
            writer.println("-- 数据插入完成");
            writer.printf("-- 统计: 省份 %d 个, 城市 %d 个, 区县 %d 个%n", provinces.size(), cities.size(), districts.size());
        }
        
        System.out.println("PostgreSQL SQL文件已生成: " + filePath);
    }
}
